A frequent requirement in database applications is to perform some processing based on a change in data. There are two common solutions to this problem.
One common solution is for a processing program to issue the data changes and then initiate the processing. One disadvantage of this approach is that it is avoidable; that is, the data processing occurs only if the program that is to process the data is the same program that changed the data. That is, if the data is changed by some other code paths or program, then the desired associated processing does not happen. This disadvantage of this solution leads to the “all data changes must happen in our data layer” approach, i.e. all data processing and data changes must occur within a part of the system environment controlled by one party. This is in conflict with an open, flexible system design.
A second common solution to the problem of performing processing based on a change in data is to put code in a trigger to detect the change and perform the processing. This solution catches all data changes but has a different disadvantage; that is, it makes the underlying SQL transaction resulting from the trigger much more complex. This lengthens its processing time, and increases the probability of a deadlock and rollback. Deadlock occurs when two processes are attempting to update the same data, and neither can proceed to completion because the data each process is attempting to update is locked by the other process. Rollback is when an SQL transaction, or a set of SQL actions, such as, insert, update, and delete, is undone and the database returns to the state the database was in before the transaction started.
For many commercial enterprises, it is a challenge to implement information technology strategies that can deal effectively with the constantly changing and accelerating data requirements of the business world, especially in the financial services area. Effective and efficient systems and processes are essential to the success of any business that needs to capture, store, and manipulate transaction data and, for audit purposes, to track and report changes made to the data.
In view of the problems described above, improvements are needed that can cure the deficiencies of conventional systems for auditing data changes in transaction systems, including prior approaches to using audit triggers.